/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package pe.gob.pnp.despachadores;

import costamar.zeus.commons.FechaHora;
import java.sql.*;
import java.util.*;
import oracle.jdbc.*;
import pe.gob.pnp.actions.*;
import pe.gob.pnp.factories.*;

/**
 *
 * @author Gregorovichz Carlos Rossi
 * @date   31/03/2010
 */
public class TramiteDespachador {


    public static List<Tramite> obtenerTramite(int idtramite) throws SQLException {
        List<Tramite> lstTramite = new ArrayList<Tramite>();
        Connection conn = null;
        try {
            conn = ConnectionPool.obtenerConexion();
            String sql = "SELECT T.NANIO_IDENTIFICADOR,T.NTRAM_IDENTIFICADOR,T.NTRAM_NUMERO,";
            sql += " T.DTRAM_FECHACREACION,T.DTRAM_FECHAATENCION,";
            sql += " T.CTRAM_ASUNTO,T.CTRAM_ASUNTO,T.NTIMO_IDENTIFICADOR,T.NPERS_ADMINISTRADO,";
            sql += " T.NUSUA_IDENTIFICADOR,T.NUSUA_IDENTIFICADOR,T.NDEPE_IDENTIFICADOR,T.NESTR_IDENTIFICADOR,";
            sql += " T.NACTR_IDENTIFICADOR,T.NDEPE_DESTINO_IDENTIFICADOR,T.CTRAM_HOJA_TRAMITE,";
            sql += " T.CTRAM_OBSERVACIONES,T.CTRAM_SOLICITANTE,D.NTIDO_IDENTIFICADOR,D.CDOCU_DESCRIPCION,";
            sql += " D.CDOCU_NUMERO,D.NTRAM_REFERENCIA,D.NTRAM_TRAMITEREFERENCIA,D.CDOCU_NUMEROCOMPLETO,";
            sql += " D.CDOCU_SIGLASOFICINA,D.NDOCU_NROFOLIOS FROM SISMA_TRAMITE T ";
            sql += " INNER JOIN SISMA_DOCUMENTO D ON T.NTRAM_IDENTIFICADOR= D.NTRAM_IDENTIFICADOR";
            sql += " WHERE T.NTRAM_IDENTIFICADOR=? AND D.NDOCU_ESPRINCIPAL=1 ";
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setInt(1, idtramite);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                Tramite tramite = new Tramite();
                tramite.setNanio_identificador(rs.getInt("NANIO_IDENTIFICADOR"));
                tramite.setNtram_identificador(rs.getInt("NTRAM_IDENTIFICADOR"));
                tramite.setNtram_numero(rs.getInt("NTRAM_NUMERO"));
                tramite.setCtram_asunto(rs.getString("CTRAM_ASUNTO"));
                lstTramite.add(tramite);
            }
            rs.close();
        } finally {

            ConnectionPool.liberarConexion(conn);
        }
        return lstTramite;

    }

    public static List<Tramite> obtenerTramitesBandeja(int usuario, int estado, int dependencia,String mes, int inicio, int limite) throws SQLException {
        List<Tramite> lstTramite = new ArrayList<Tramite>();
        Connection conn = null;
        String sql = "";
        try {
            conn = ConnectionPool.obtenerConexion();
            sql = " select * from (";
            sql += " SELECT rownum limite,T.NANIO_IDENTIFICADOR,A.CANIO_ANIO, ";
            sql += " T.NTRAM_IDENTIFICADOR,T.NTRAM_NUMERO,T.DTRAM_FECHACREACION,";
            sql += " NVL(T.DTRAM_FECHAATENCION,DTRAM_FECHACREACION) AS DTRAM_FECHAATENCION, ";
            sql += " T.NPERS_ADMINISTRADO,P.CPERS_APEPATERNO || ' ' || P.CPERS_APEMATERNO || ' ' || P.CPERS_NOMBRE AS APENOM,";
            sql += " T.NUSUA_IDENTIFICADOR,U.CUSUA_USERNAME,T.NDEPE_IDENTIFICADOR,";
            sql += " T.NESTR_IDENTIFICADOR,E.CESTR_DESCRIPCION,T.CTRAM_HOJA_TRAMITE,";
            sql += " T.CTRAM_OBSERVACIONES,T.CTRAM_SOLICITANTE, M.NMOVI_IDENTIFICADOR, ";
            sql += " T.CTRAM_TIENEBENEFICIO,T.CTRAM_PRIORIDAD,T.CTRAM_HOJA_TRAMITE_SIGE ";
            sql += " FROM SISMA_TRAMITE T ";
            sql += " INNER JOIN GENMA_ANIO A ON T.NANIO_IDENTIFICADOR = A.NANIO_IDENTIFICADOR";
            sql += " AND A.BANIO_ACTIVO=1";
            sql += " LEFT JOIN PERMA_PERSONA P";
            sql += " ON T.NPERS_ADMINISTRADO = P.NPERS_IDENTIFICADOR";
            sql += " AND P.BPERS_ELIMINADO = 0";
            sql += " INNER JOIN SISMA_ESTADOTRAMITE E";
            sql += " ON E.NESTR_IDENTIFICADOR = T.NESTR_IDENTIFICADOR ";
            sql += " INNER JOIN SEGMA_USUARIO U ";
            sql += " ON T.NUSUA_IDENTIFICADOR=U.NUSUA_IDENTIFICADOR  AND U.BUSUA_ACTIVO=1";
            sql += " INNER JOIN SISMA_MOVIMIENTO M";
            sql += " ON T.NTRAM_IDENTIFICADOR= M.NTRAM_IDENTIFICADOR   AND M.NMOVI_ELIMINADO = 0";
            sql += " WHERE M.NDEPE_IDENTIFICADORINGRESO=? ";
            sql += " AND M.NESTR_IDENTIFICADOR =?";
            sql += " ) where limite between ? and ?";
            sql += " ORDER BY CTRAM_PRIORIDAD DESC,CTRAM_HOJA_TRAMITE";
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setInt(1, dependencia);
            ps.setInt(2, estado);
            ps.setInt(3,inicio);
            ps.setInt(4,inicio+limite);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                Tramite tramite = new Tramite();
                tramite.setNanio_identificador(rs.getInt("NANIO_IDENTIFICADOR"));
                tramite.setNmovi_identificador(rs.getInt("NMOVI_IDENTIFICADOR"));
                tramite.setCanio_anio(rs.getString("CANIO_ANIO"));
                tramite.setNtram_identificador(rs.getInt("NTRAM_IDENTIFICADOR"));
                tramite.setNtram_numero(rs.getInt("NTRAM_NUMERO"));
                tramite.setCtram_prioridad(rs.getString("CTRAM_PRIORIDAD"));
                tramite.setCtram_tienebeneficio(rs.getString("CTRAM_TIENEBENEFICIO"));
                tramite.setCtram_hojatramitesige(rs.getString("CTRAM_HOJA_TRAMITE_SIGE"));
                String date = new java.text.SimpleDateFormat("dd/MM/yyyy HH:mm:ss").format(rs.getTimestamp("DTRAM_FECHACREACION"));
                tramite.setDtram_fechacreacion(date);
                tramite.setDtram_fechaatencion(rs.getDate("DTRAM_FECHAATENCION"));
                tramite.setNpers_administrado(rs.getInt("NPERS_ADMINISTRADO"));
                tramite.setCpers_apenom(rs.getString("APENOM"));
                tramite.setNusua_identificador(rs.getInt("NUSUA_IDENTIFICADOR"));
                tramite.setCusua_username(rs.getString("CUSUA_USERNAME"));
                tramite.setNdepe_identificador(rs.getInt("NDEPE_IDENTIFICADOR"));
                tramite.setNestr_identificador(rs.getInt("NESTR_IDENTIFICADOR"));
                tramite.setCestr_descripcion(rs.getString("CESTR_DESCRIPCION"));
                tramite.setCtram_hojaTramite(rs.getString("CTRAM_HOJA_TRAMITE"));
                tramite.setCtram_observaciones(rs.getString("CTRAM_OBSERVACIONES"));
                tramite.setCtram_solicitante(rs.getString("CTRAM_SOLICITANTE"));
                String solicita = obtenerBeneficioSolicitaTramite(rs.getInt("NTRAM_IDENTIFICADOR"));
                tramite.setNsolicitabeneficio(solicita);
                lstTramite.add(tramite);
            }
            rs.close();
        } finally {
            ConnectionPool.liberarConexion(conn);
        }
        return lstTramite;
    }

    public static List<Tramite> consultaTramite(int opcion, String criterio) throws SQLException {
        List<Tramite> lstTramite = new ArrayList<Tramite>();
        Connection conn = null;
        StringBuilder sql = null;
        try {
            conn = ConnectionPool.obtenerConexion();
            sql = new StringBuilder();
            sql.append("SELECT T.NANIO_IDENTIFICADOR,A.CANIO_ANIO, ");
            sql.append(" T.NTRAM_IDENTIFICADOR,T.NTRAM_NUMERO,T.DTRAM_FECHACREACION,");
            sql.append(" NVL(T.DTRAM_FECHAATENCION,DTRAM_FECHACREACION) AS DTRAM_FECHAATENCION, ");
            sql.append(" T.NPERS_ADMINISTRADO,  TRIM(NVL(P.CPERS_APEPATERNO,'') || ' ' || NVL(P.CPERS_APEMATERNO,'') || ' ' || NVL(P.CPERS_NOMBRE,'PENDIENTE')) AS APENOM,");
            sql.append(" T.NUSUA_IDENTIFICADOR,U.CUSUA_USERNAME,T.NDEPE_IDENTIFICADOR,");
            sql.append(" T.NESTR_IDENTIFICADOR,E.CESTR_DESCRIPCION,T.CTRAM_HOJA_TRAMITE,");
            sql.append(" NVL(T.CTRAM_OBSERVACIONES,'NO REGISTRADO') AS CTRAM_OBSERVACIONES , NVL(T.CTRAM_SOLICITANTE,'NO REGISTRADO') AS CTRAM_SOLICITANTE  ,");
            sql.append(" T.CTRAM_TIENEBENEFICIO,T.CTRAM_PRIORIDAD,T.CTRAM_HOJA_TRAMITE_SIGE ");
            sql.append(" FROM SISMA_TRAMITE T ");
            sql.append(" INNER JOIN GENMA_ANIO A ON T.NANIO_IDENTIFICADOR = A.NANIO_IDENTIFICADOR");
            sql.append(" LEFT JOIN PERMA_PERSONA P");
            sql.append(" ON T.NPERS_ADMINISTRADO = P.NPERS_IDENTIFICADOR");
            sql.append(" INNER JOIN SISMA_ESTADOTRAMITE E");
            sql.append(" ON E.NESTR_IDENTIFICADOR = T.NESTR_IDENTIFICADOR ");
            sql.append(" INNER JOIN SEGMA_USUARIO U ");
            sql.append(" ON U.NUSUA_IDENTIFICADOR = T.NUSUA_IDENTIFICADOR");
            sql.append(" WHERE ");
            if (opcion == 1) {
                sql.append(" T.CTRAM_HOJA_TRAMITE  = '").append(criterio).append("'");
            }
            if (opcion == 2) {
                sql.append(" T.CTRAM_HOJA_TRAMITE_SIGE = '").append(criterio).append("'");
            }
            if (opcion == 3) {
                sql.append(" (('").append(criterio).append("' IS NULL OR UPPER(P.CPERS_APEPATERNO) LIKE '%' || UPPER('").append(criterio).append("') || '%') ");
                sql.append(" OR ('").append(criterio).append("' IS NULL OR UPPER(P.CPERS_APEMATERNO) LIKE '%' || UPPER('").append(criterio).append("') || '%') ");
                sql.append(" OR ('").append(criterio).append("' IS NULL OR UPPER(P.CPERS_NOMBRE) LIKE '%' || UPPER('").append(criterio).append("') || '%')) ");
            }
            sql.append(" ORDER BY T.CTRAM_PRIORIDAD DESC,T.NPERS_ADMINISTRADO ,T.CTRAM_HOJA_TRAMITE");

            PreparedStatement ps = conn.prepareStatement(sql.toString());
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                Tramite tramite = new Tramite();
                tramite.setNanio_identificador(rs.getInt("NANIO_IDENTIFICADOR"));
                // tramite.setNmovi_identificador(rs.getInt("NMOVI_IDENTIFICADOR"));
                tramite.setCanio_anio(rs.getString("CANIO_ANIO"));
                tramite.setNtram_identificador(rs.getInt("NTRAM_IDENTIFICADOR"));
                tramite.setNtram_numero(rs.getInt("NTRAM_NUMERO"));
                tramite.setCtram_prioridad(rs.getString("CTRAM_PRIORIDAD"));
                tramite.setCtram_tienebeneficio(rs.getString("CTRAM_TIENEBENEFICIO"));
                tramite.setCtram_hojatramitesige(rs.getString("CTRAM_HOJA_TRAMITE_SIGE"));
                String date = new java.text.SimpleDateFormat("dd/MM/yyyy HH:mm:ss").format(rs.getTimestamp("DTRAM_FECHACREACION"));
                tramite.setDtram_fechacreacion(date);
                tramite.setDtram_fechaatencion(rs.getDate("DTRAM_FECHAATENCION"));
                tramite.setNpers_administrado(rs.getInt("NPERS_ADMINISTRADO"));
                tramite.setCpers_apenom(rs.getString("APENOM"));
                tramite.setNusua_identificador(rs.getInt("NUSUA_IDENTIFICADOR"));
                tramite.setCusua_username(rs.getString("CUSUA_USERNAME"));
                tramite.setNdepe_identificador(rs.getInt("NDEPE_IDENTIFICADOR"));
                tramite.setNestr_identificador(rs.getInt("NESTR_IDENTIFICADOR"));
                tramite.setCestr_descripcion(rs.getString("CESTR_DESCRIPCION"));
                tramite.setCtram_hojaTramite(rs.getString("CTRAM_HOJA_TRAMITE"));
                tramite.setCtram_observaciones(rs.getString("CTRAM_OBSERVACIONES"));
                tramite.setCtram_solicitante(rs.getString("CTRAM_SOLICITANTE"));
                String solicita = obtenerBeneficioSolicitaTramite(rs.getInt("NTRAM_IDENTIFICADOR"));
                tramite.setNsolicitabeneficio(solicita);
                lstTramite.add(tramite);
            }
            rs.close();
        } finally {
            System.out.println("tramiteDespachador. 151 " + sql.toString());
            ConnectionPool.liberarConexion(conn);
        }
        return lstTramite;
    }

    public static String obtenerBeneficioSolicitaTramite(int tramite) throws SQLException {
        String beneficios = ""; //SOLICITA :
        String sql = "SELECT B.NTIBE_IDENTIFICADOR,B.CTIBE_DESCRIPCION ";
        sql += " FROM SISMA_BENEFICIOSOLICITA BS ";
        sql += " INNER JOIN PERMA_TIPOBENEFICIO B ";
        sql += " ON B.NTIBE_IDENTIFICADOR = BS.NTIBE_IDENTIFICADOR";
        sql += " WHERE BS.NTRAM_IDENTIFICADOR=?";
        Connection conn = null;
        try {
            conn = ConnectionPool.obtenerConexion();
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setInt(1, tramite);
            ResultSet rs = ps.executeQuery();
            int i = 0;
            while (rs.next()) {
                if (i > 0) {
                    beneficios += ",";
                }
                beneficios += rs.getString("CTIBE_DESCRIPCION");
                i++;
            }
            rs.close();
        } finally {
            ConnectionPool.liberarConexion(conn);
        }
        return beneficios;
    }

    public static List<Tramite> obtenerTramitesEstado(int usuario, int estado, int dependencia) throws SQLException {
        List<Tramite> lstTramite = new ArrayList<Tramite>();
        Connection conn = null;
        try {
            conn = ConnectionPool.obtenerConexion();
            String sql = "{call SP_OBTENER_TRAMITES(?,?,?,?,?)}";
            CallableStatement cs = conn.prepareCall(sql);
            cs.setInt(1, estado);
            cs.setInt(2, usuario);
            cs.setInt(3, 1);
            cs.registerOutParameter(4, OracleTypes.CURSOR);
            cs.setInt(5, dependencia);
            cs.execute();
            ResultSet rs = (ResultSet) cs.getObject(4);
            while (rs.next()) {
                Tramite tramite = new Tramite();
                tramite.setCdepe_nombre(rs.getString("DESTINO"));
                tramite.setCestr_descripcion(rs.getString("CESTR_DESCRIPCION"));
                tramite.setNpers_administrado(rs.getInt("NPERS_ADMINISTRADO"));
                tramite.setCpers_apematerno(rs.getString("CPERS_APEMATERNO"));
                tramite.setCpers_apepaterno(rs.getString("CPERS_APEPATERNO"));
                tramite.setCpers_nombre(rs.getString("CPERS_NOMBRE"));
                tramite.setCtram_asunto(rs.getString("CTRAM_ASUNTO"));
                tramite.setCusua_username(rs.getString("CUSUA_USERNAME"));
                tramite.setNdepe_identificador(rs.getInt("NDEPE_IDENTIFICADOR"));
                tramite.setNestr_identificador(rs.getInt("NESTR_IDENTIFICADOR"));
                tramite.setNtram_numero(rs.getInt("NTRAM_NUMERO"));
                tramite.setNusua_identificador(rs.getInt("NUSUA_IDENTIFICADOR"));
                tramite.setNtram_identificador(rs.getInt("NTRAM_IDENTIFICADOR"));
                tramite.setNtram_referencia(rs.getInt("NTRAM_TRAMITEREFERENCIA"));
                tramite.setNdepe_destino(rs.getInt("NDEPE_DESTINO_IDENTIFICADOR"));
                tramite.setDtram_fechacreacion(rs.getString("DTRAM_FECHACREACION"));
                lstTramite.add(tramite);
            }
            rs.close();
        } finally {
            ConnectionPool.liberarConexion(conn);
        }
        return lstTramite;
    }

    public static List<DocumentoTramite> obtenerDocumentosTramite(int tramite) throws SQLException {
        List<DocumentoTramite> lstDocumentoTramite = new ArrayList<DocumentoTramite>();
        Connection conn = null;
        try {
            conn = ConnectionPool.obtenerConexion();
            String sql = "{call SP_OBTENER_DOCUMENTOTRAMITE(?,?)}";
            CallableStatement cs = conn.prepareCall(sql);
            cs.setInt(1, tramite);
            cs.registerOutParameter(2, OracleTypes.CURSOR);
            cs.execute();
            ResultSet rs = (ResultSet) cs.getObject(2);
            while (rs.next()) {
                DocumentoTramite documento = new DocumentoTramite();
                documento.setNtidoIdentificador(rs.getInt("NTIDO_IDENTIFICADOR"));
                documento.setCtido_descripcion(rs.getString("CTIDO_DESCRIPCION"));
                documento.setNdepeIdentificadoringreso(rs.getInt("NDEPE_IDENTIFICADORINGRESO"));
                String date = new java.text.SimpleDateFormat("dd/MM/yyyy HH:mm:ss").format(rs.getTimestamp("DDOCU_FECHACREACION"));
                documento.setDdocuFechacreacion(date);
                documento.setNdocuEsprincipal(rs.getInt("NDOCU_ESPRINCIPAL"));
                documento.setNtramIdentificador(rs.getInt("NTRAM_IDENTIFICADOR"));
                documento.setNdocuIdentificador(rs.getInt("NDOCU_IDENTIFICADOR"));
                documento.setNesdoIdentificador(rs.getInt("NESDO_IDENTIFICADOR"));
                documento.setCdocu_numero(rs.getString("CDOCU_NUMERO"));
                documento.setNtramReferencia(rs.getString("NTRAM_TRAMITEREFERENCIA"));
                documento.setNanioIdentificador(rs.getInt("NANIO_IDENTIFICADOR"));
                documento.setCanioAnio(rs.getString("CANIO_ANIO"));
                documento.setCdocuNumeroCompleto(rs.getString("CDOCU_NUMEROCOMPLETO"));
                documento.setCdocuSiglasOficina(rs.getString("CDOCU_SIGLASOFICINA"));
                documento.setNdocuNroFolios(rs.getInt("NDOCU_NROFOLIOS"));
                documento.setCdocu_descripcion(rs.getString("CDOCU_DESCRIPCION"));
                lstDocumentoTramite.add(documento);
            }
            rs.close();
        } finally {
            ConnectionPool.liberarConexion(conn);
        }
        return lstDocumentoTramite;
    }

    public static List<Movimiento> obtenerRutaTramite(int tramite) throws SQLException {
        List<Movimiento> lstMovimiento = new ArrayList<Movimiento>();
        Connection conn = null;
        try {
            conn = ConnectionPool.obtenerConexion();
            String sql = "{call SP_OBTENER_RUTATRAMITE(?,?)}";
            CallableStatement cs = conn.prepareCall(sql);
            cs.setInt(1, tramite);
            cs.registerOutParameter(2, OracleTypes.CURSOR);
            cs.execute();
            ResultSet rs = (ResultSet) cs.getObject(2);
            while (rs.next()) {
                Movimiento movimiento = new Movimiento();
                movimiento.setCdepeDestino(rs.getString("DESTINO"));
                movimiento.setCdepeOrigen(rs.getString("ORIGEN"));
                movimiento.setCesmoDescripcion(rs.getString("CESMO_DESCRIPCION"));
                movimiento.setDmoviFechaCreacion(rs.getDate("FECHACREACION"));
                String date = new java.text.SimpleDateFormat("dd/MM/yyyy HH:mm:ss").format(rs.getTimestamp("FECHAENVIO"));
                movimiento.setDmoviFechaEnvio(date);
                movimiento.setNesmoIdentificador(rs.getInt("NESMO_IDENTIFICADOR"));
                movimiento.setNmoviIdentificador(rs.getInt("NMOVI_IDENTIFICADOR"));
                movimiento.setDmoviFechaRecepcion(rs.getString("FECHARECEPCION"));
                lstMovimiento.add(movimiento);
            }
            rs.close();
        } finally {
            ConnectionPool.liberarConexion(conn);
        }
        return lstMovimiento;
    }

    public static void agregarTramite(Tramite tramite) throws SQLException {
        Connection conn = null;
        try {
            conn = ConnectionPool.obtenerConexion();
            String sql = "{call SP_AGREGAR_TRAMITE(?,?,?,?,?,?,?,?,?,?,?,?,?,?)}";
            CallableStatement cs = conn.prepareCall(sql);
            cs.setInt(1, tramite.getNtram_numero());
            cs.setObject(2, null);
            cs.setString(3, tramite.getCtram_asunto());
            cs.setInt(4, tramite.getNtimo_identificador());
            cs.setInt(5, tramite.getNpers_administrado());
            cs.setInt(6, tramite.getNusua_identificador());
            cs.setInt(7, tramite.getNdepe_identificador());
            cs.setInt(8, tramite.getNestr_identificador());
            cs.setString(9, tramite.getCtram_asunto());
            cs.setString(10, null);
            cs.setString(11, tramite.getCtram_hojaTramite());
            cs.setInt(12, tramite.getNdepe_destino());
            cs.setInt(13, tramite.getCnext_estado_tramite());
            cs.setInt(14, tramite.getCbotipodocumento());
            cs.execute();

        } finally {
            ConnectionPool.liberarConexion(conn);
        }
    }

    public static Tramite insertarTramite(Tramite tramite) throws SQLException {
        Connection conn = null;
        boolean flag = false;
        Tramite tramitenuevo = new Tramite();
        FechaHora fechaHora = new FechaHora();
        String hojaTramiteCabecera = "" + fechaHora.getIntFechaSistemaAMD();
        tramitenuevo.setFlag(false);
        try {
            conn = ConnectionPool.obtenerConexion();
            String sql = "{call SP_AGREGAR_TRAMITEDOC(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}";
            CallableStatement cs = conn.prepareCall(sql);
            cs.setInt(1, tramite.getNtram_numero());
            cs.setInt(2, tramite.getNtimo_identificador());
            cs.setInt(3, tramite.getNpers_administrado());
            cs.setInt(4, tramite.getNusua_identificador());
            cs.setInt(5, tramite.getNdepe_identificador());
            cs.setInt(6, tramite.getNestr_identificador());
            cs.setString(7, hojaTramiteCabecera);
            cs.setString(8, tramite.getCtram_observaciones());
            cs.setString(9, tramite.getCtram_solicitante());
            cs.setInt(10, tramite.getCbotipodocumento());
            cs.setString(11, tramite.getCdocu_numero());
            cs.setString(12, tramite.getCdocunumerocompleto());
            cs.setString(13, tramite.getCsiglasoficina());
            cs.setInt(14, tramite.getNdocu_folios());
            cs.setString(15, tramite.getFechasolicitud());
            cs.setString(16, tramite.getTelefonosolicitante());
            cs.registerOutParameter(17, OracleTypes.INTEGER);
            cs.setString(18, tramite.getCtram_hojatramitesige());
            cs.setString(19, tramite.getCtram_tienebeneficio());
            cs.setString(20, tramite.getCtram_prioridad());
            cs.execute();
            int numerotramite = (Integer) cs.getObject(17);
            StringTokenizer tokensacciones = new StringTokenizer(tramite.getNaccionestramite(), ",");
            tokensacciones.nextToken();
            while (tokensacciones.hasMoreTokens()) {
                AccionTramite accion = new AccionTramite();
                accion.setNtramIdentificador(numerotramite);
                accion.setNactrIdentificador(Integer.parseInt(tokensacciones.nextToken()));
                AccionesTramiteDespachador.agregarAccionTramite(accion);
            }
            StringTokenizer tokensbeneficios = new StringTokenizer(tramite.getNsolicitabeneficio(), ",");
            tokensbeneficios.nextToken();
            while (tokensbeneficios.hasMoreTokens()) {
                BeneficioSolicitaBean solicita = new BeneficioSolicitaBean();
                solicita.setNtramidentificador(numerotramite);
                solicita.setNtibeidentificador(Integer.parseInt(tokensbeneficios.nextToken()));
                BeneficioDespachador.agregarBeneficioTramite(solicita);
            }
            tramitenuevo.setNtram_identificador(numerotramite);
            tramitenuevo.setFlag(true);
            flag = true;
        } finally {
            ConnectionPool.liberarConexion(conn);
        }
        return tramitenuevo;
    }

    public static void derivarTramite(Tramite tramite) throws SQLException {
        Connection conn = null;
        try {
            conn = ConnectionPool.obtenerConexion();
            String sql = "{call SP_DERIVAR_TRAMITE(?,?,?,?,?,?,?,?,?)}";
            CallableStatement cs = conn.prepareCall(sql);
            cs.setInt(1, tramite.getNtram_identificador());
            cs.setInt(2, tramite.getNtram_numero());
            cs.setString(3, tramite.getCtram_asunto());
            cs.setInt(4, tramite.getNdepe_identificador());
            cs.setInt(5, tramite.getNdepe_destino());
            cs.setInt(6, tramite.getNesmo_inicial());
            cs.setInt(7, tramite.getNesmo_final());
            cs.setInt(8, tramite.getNpers_administrado());
            cs.setInt(9, tramite.getNusua_identificador());
            cs.execute();
        } finally {
            ConnectionPool.liberarConexion(conn);
        }
    }

    public static void derivarTramiteDoc(Tramite tramite) throws SQLException {
        Connection conn = null;
        try {
            conn = ConnectionPool.obtenerConexion();
            String sql = "{call SP_DERIVAR_TRAMITEDOC(?,?,?,?,?)}";
            CallableStatement cs = conn.prepareCall(sql);
            cs.setInt(1, tramite.getNtram_identificador());
            cs.setInt(2, 1);
            cs.setInt(3, tramite.getNdepe_destino());
            cs.setInt(4, tramite.getNusua_identificador());
            cs.setInt(5, tramite.getNdepe_identificador());
            cs.execute();
        } finally {
            ConnectionPool.liberarConexion(conn);
        }
    }

    public static void recibirTramite(int idtramite, int origen, int destino) throws SQLException {
        Connection conn = null;
        try {
            conn = ConnectionPool.obtenerConexion();
            String sql = "{call SP_RECIBIR_TRAMITE(?)}";
            CallableStatement cs = conn.prepareCall(sql);
            cs.setInt(1, idtramite);
            cs.execute();
        } finally {
            ConnectionPool.liberarConexion(conn);
        }
    }

    public static boolean anexarDocumento(DocumentoTramite documento) throws SQLException {
        Connection conn = null;
        boolean flag = false;
        try {
            System.out.println("" + documento.getNdocuIdentificador());
            conn = ConnectionPool.obtenerConexion();
            String sql = "{call SP_ANEXAR_DOCUMENTO(?,?,?,?,?,?,?,?,?,?)}";
            CallableStatement cs = conn.prepareCall(sql);
            cs.setInt(1, documento.getNtidoIdentificador());
            cs.setInt(2, documento.getNtramIdentificador());
            cs.setString(3, documento.getCdocu_numero());
            cs.setString(4, documento.getCdocuNumeroCompleto());
            cs.setString(5, documento.getCdocuSiglasOficina());
            cs.setInt(6, documento.getNdocuNroFolios());
            cs.setInt(7, documento.getNdepeIdentificadoringreso());
            cs.setString(8, documento.getCdocu_descripcion());
            cs.setString(9, documento.getAccion());
            cs.setInt(10, documento.getNdocuIdentificador());
            cs.execute();
            flag = true;
        } finally {
            ConnectionPool.liberarConexion(conn);
        }
        return flag;
    }

    public static boolean modificarTramite(int nmovi_identificador, int nestr_identificador) {
        Connection conn = null;
        boolean flag = false;
        try {
            conn = ConnectionPool.obtenerConexion();
            String sql = "UPDATE SISMA_MOVIMIENTO SET NESTR_IDENTIFICADOR = ?,NESMO_IDENTIFICADOR=?,";
            sql += " DMOVI_FECHARECEPCION=SYSDATE";
            sql += " WHERE NMOVI_IDENTIFICADOR = ?";
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setInt(1, nestr_identificador);
            ps.setInt(2, nestr_identificador);
            ps.setInt(3, nmovi_identificador);
            ps.executeUpdate();
            flag = true;
        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            ConnectionPool.liberarConexion(conn);
        }
        return flag;
    }

    public static String obtenerHTTramite() {
        Connection conn = null;
        String ht = "0000001";
        try {
            conn = ConnectionPool.obtenerConexion();
            //String sql = "SELECT LPAD(NVL(MAX(to_number(NTRAM_NUMERO)),0)+1,7,'0') AS  NTRAM_NUMERO FROM SISMA_TRAMITE";
            String sql = "SELECT LPAD(NVL(seq_ntram_identificador.nextval,0)+1,7,'0') AS  NTRAM_NUMERO FROM dual";
            PreparedStatement ps = conn.prepareStatement(sql);
            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                ht = rs.getString("NTRAM_NUMERO");
            }
            rs.close();
        } catch (Exception ex) {
            ht = "0000001";
            ex.printStackTrace();
        } finally {
            ConnectionPool.liberarConexion(conn);
        }
        return ht;
    }

    public static String obtenerIDDocumento(int dependencia, int tipodocumento) {
        Connection conn = null;
        String iddocumento = "0000001";
        try {
            conn = ConnectionPool.obtenerConexion();
            String sql = "SELECT LPAD(NVL(MAX(CDOCU_NUMERO),0)+1,7,'0') AS CDOCU_NUMERO FROM SISMA_DOCUMENTO ";
            sql += " WHERE NDEPE_IDENTIFICADORINGRESO= ?";
            sql += " AND NTIDO_IDENTIFICADOR=?";
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setInt(1, dependencia);
            ps.setInt(2, tipodocumento);
            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                iddocumento = rs.getString("CDOCU_NUMERO");
            }
            rs.close();
        } catch (Exception ex) {
            iddocumento = "0000001";
            ex.printStackTrace();
        } finally {
            ConnectionPool.liberarConexion(conn);
        }
        return iddocumento;
    }

    public static int obtenerNumeroTotalDeFolios(int ntramcodigo) {
        Connection conn = null;
        int folios = 0;
        StringBuilder sql = new StringBuilder();
        try {
            conn = ConnectionPool.obtenerConexion();
            sql.append(" select sum( ndocu_nrofolios) as folios  from sisma_tramite t inner join sisma_documento d on d.ntram_identificador = t.ntram_identificador ");
            sql.append(" where t.ntram_identificador = ? ");
            sql.append(" group by t.ntram_identificador order by t.ntram_identificador desc ");
            PreparedStatement ps = conn.prepareStatement(sql.toString());
            ps.setInt(1, ntramcodigo);
            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                folios = rs.getInt("folios");
            }
            rs.close();
        } catch (Exception ex) {
            folios = 0;
            ex.printStackTrace();
        } finally {
            ConnectionPool.liberarConexion(conn);
        }
        return folios;
    }

    public static boolean existeMovimientos(int ntramcodigo) {
        Connection conn = null;
        String sql = "";
        boolean flag = false;
        try {
            conn = ConnectionPool.obtenerConexion();
            sql = "select count(*) as cnt from sisma_movimiento where NTRAM_IDENTIFICADOR=?";
            PreparedStatement ps = conn.prepareStatement(sql.toString());
            ps.setInt(1, ntramcodigo);
            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                if (rs.getInt("cnt") > 1) {
                    flag = true;
                } else {
                    flag = false;
                }
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            ConnectionPool.liberarConexion(conn);
        }
        return flag;
    }

    public static boolean soloMovimientosSinRecibir(int ntramcodigo) {
        Connection conn = null;
        String sql = "";
        boolean flag = false;
        try {
            conn = ConnectionPool.obtenerConexion();
            sql = "select count(*) as cnt from sisma_movimiento where NTRAM_IDENTIFICADOR=?";
            PreparedStatement ps = conn.prepareStatement(sql.toString());
            ps.setInt(1, ntramcodigo);
            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                if (rs.getInt("cnt") == 2) {
                    flag = true;
                } else {
                    flag = false;
                }
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            ConnectionPool.liberarConexion(conn);
        }
        return flag;
    }

    public static boolean eliminarTramiteSinMovimientos(int ntramcodigo) {
        Connection conn = null;
        String sql = "";
        boolean flag = false;
        try {
            conn = ConnectionPool.obtenerConexion();
            sql = "delete from sisma_movimiento where ntram_identificador = ?";
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setInt(1, ntramcodigo);
            ps.executeUpdate();
            sql = "delete from sisde_tramiteaccion where ntram_identificador = ?";
            ps = conn.prepareStatement(sql);
            ps.setInt(1, ntramcodigo);
            sql = "delete from sisma_beneficiosolicita where NTRAM_IDENTIFICADOR=?";
            ps = conn.prepareStatement(sql);
            ps.setInt(1, ntramcodigo);
            ps.executeUpdate();
            sql = "delete from sisma_tramite where ntram_identificador = ?";
            ps = conn.prepareStatement(sql);
            ps.setInt(1, ntramcodigo);
            ps.executeUpdate();
            flag = true;
        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            ConnectionPool.liberarConexion(conn);
        }
        return flag;
    }

    public static boolean eliminarMovimientos(int nmovicodigo) {
        Connection conn = null;
        String sql = "";
        boolean flag = false;
        try {
            conn = ConnectionPool.obtenerConexion();
            System.out.println(nmovicodigo);
            sql = "select ntram_identificador, ndepe_identificadoringreso ";
            sql += " from sisma_movimiento where nmovi_identificador = ? ";
            sql += " and nesmo_identificador = 1";
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setInt(1, nmovicodigo);
            ResultSet rs = ps.executeQuery();
            if (rs.next()){
                int tramite = rs.getInt("ntram_identificador");
                int dependencia = rs.getInt("ndepe_identificadoringreso");
                sql = "select nmovi_identificador from sisma_movimiento ";
                sql += "where ntram_identificador = ? and nesmo_identificador = 5 ";
                sql += " and  ndepe_identificadorsalida = ?";
                ps = conn.prepareStatement(sql);
                ps.setInt(1, tramite);
                ps.setInt(2, dependencia);
                rs = ps.executeQuery();
                if (rs.next()){
                    int movimientosalida = rs.getInt("nmovi_identificador");
                    //Actualizo Situacion tramite
                    sql = "update sisma_tramite set nestr_identificador = 8  ";
                    sql += " where ntram_identificador = ?";
                    ps = conn.prepareStatement(sql);
                    ps.setInt(1, tramite);
                    ps.executeUpdate();
                    //elimino el derivo
                    sql = "delete from sisma_movimiento where nmovi_identificador = ?";
                    sql += " and nesmo_identificador = 1";
                    ps = conn.prepareStatement(sql);
                    ps.setInt(1,nmovicodigo);
                    ps.executeUpdate();
                    //actualizo el origen
                    sql = "update sisma_movimiento set ndepe_identificadorsalida =null,";
                    sql += " nesmo_identificador = 8,nestr_identificador=8";
                    sql += " where nmovi_identificador = ? and nesmo_identificador = 5 ";
                    ps = conn.prepareStatement(sql);
                    ps.setInt(1, movimientosalida);
                    ps.executeUpdate();
                    flag = true;
                }
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            ConnectionPool.liberarConexion(conn);
        }
        return flag;
    }

    public static boolean verificaCorrespondenciaMovimientos(int nmovicodigo,int ndepecodigo) {
        Connection conn = null;
        String sql = "";
        boolean flag = false;
        try {
            conn = ConnectionPool.obtenerConexion();
            sql = "select ntram_identificador,ndepe_identificadoringreso";
            sql += " from sisma_movimiento where nmovi_identificador = ?";
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setInt(1, nmovicodigo);
            ResultSet rs = ps.executeQuery();
            if (rs.next()){
              int tramite = rs.getInt("ntram_identificador");
              int depe = rs.getInt("ndepe_identificadoringreso");
              sql = "select count(ndepe_identificadoringreso) as cnt from sisma_movimiento";
              sql += " where ntram_identificador =?";
              sql += " and nesmo_identificador = 5 ";
              sql += " and  ndepe_identificadorsalida = ?";
              sql += " and ndepe_identificadoringreso = ?";
              ps = conn.prepareStatement(sql);
              ps.setInt(1, tramite);
              ps.setInt(2,depe);
              ps.setInt(3, ndepecodigo);
              rs = ps.executeQuery();
              if (rs.next()){
                  if (rs.getInt("cnt")>0){
                      flag=true;
                  }else{
                      flag = false;
                  }
              }
            }
            System.out.println(flag);
        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            ConnectionPool.liberarConexion(conn);
        }
        return flag;
    }


     public static List<NumeroExpedienteBean> obtenerNumeracionExpediente() throws SQLException {
        List<NumeroExpedienteBean> lstNumeracion = new ArrayList<NumeroExpedienteBean>();
        Connection conn = null;
        try {
            conn = ConnectionPool.obtenerConexion();
            String sql = "SELECT NEX.NNUEX_IDENTIFICADOR,NEX.NTIPP_IDENTIFICADOR,";
            sql += "TPP.CTIPP_DESCRIPCION,NNUEX_LIMITEINFERIOR,NEX.NNUEX_VALORACTUAL,NEX.NUSUA_IDENTIFICADOR ";
            sql += " FROM SISMA_NUMERACIONEXPEDIENTE NEX";
            sql += " INNER JOIN SISMA_TIPOPERSONALPOLICIAL TPP";
            sql += " ON TPP.NTIPP_IDENTIFICADOR = NEX.NTIPP_IDENTIFICADOR";
            sql += " ORDER BY NEX.NTIPP_IDENTIFICADOR";
            PreparedStatement ps = conn.prepareStatement(sql);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                NumeroExpedienteBean numeracion = new NumeroExpedienteBean();
                numeracion.setNnuex_identificador(rs.getInt("NNUEX_IDENTIFICADOR"));
                numeracion.setNtipp_identificador(rs.getInt("NTIPP_IDENTIFICADOR"));
                numeracion.setCtipp_descripcion(rs.getString("CTIPP_DESCRIPCION"));
                numeracion.setNnuex_limiteinferior(rs.getInt("NNUEX_LIMITEINFERIOR"));
                numeracion.setNnuex_valoractual(rs.getInt("NNUEX_VALORACTUAL"));
                numeracion.setNusua_identificador(rs.getInt("NUSUA_IDENTIFICADOR"));
                lstNumeracion.add(numeracion);
            }
            rs.close();
        } finally {

            ConnectionPool.liberarConexion(conn);
        }
        return lstNumeracion;

    }


       public static boolean actualizarNumeracionExpediente(int nnuexidentificador,int nnuexlimite,int nnuexvalor) {
        Connection conn = null;
        String sql = "";
        boolean flag = false;
        try {
            conn = ConnectionPool.obtenerConexion();
            sql = "update sisma_numeracionexpediente";
            sql += " set nnuex_limiteinferior = ?,NNUEX_VALORACTUAL=? ";
            sql += " where NNUEX_IDENTIFICADOR=?";
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setInt(1, nnuexlimite);
            ps.setInt(2, nnuexvalor);
            ps.setInt(3, nnuexidentificador);
            ps.executeUpdate();
            flag = true;
        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            ConnectionPool.liberarConexion(conn);
        }
        return flag;
    }

}
